# 依据 排班表，从考勤 表中统计出 每个人 对应日期的 有不有早退 迟到 缺勤，并在最后添加3列统计早退 迟到 缺勤的次数

from sre_compile import isstring
from traceback import print_tb
import xlrd

import datetime
import time


def gettime(string):
    if ("休" in string) or (not string) or "-" in string:
        return ""
    if "次" in string:
        return datetime.datetime.strptime("23:00", "%H:%M")
    else:
        return datetime.datetime.strptime(string.strip(), "%H:%M")


# 排班表
wa = xlrd.open_workbook("./a.xlsx")
wst_a = wa.sheet_by_index(0)
tbA = dict()

for r in range(4, wst_a.nrows):
    # 提取有效日期数据
    tdata = [x.strip(";").split(";") for x in wst_a.row_values(r)[4 : wst_a.ncols]]
    tbA[wst_a.row_values(r)[0]] = [[gettime(x[0]), gettime(x[-1])] for x in tdata]

# 考勤表
wb = xlrd.open_workbook("./b.xlsx")
wst_b = wb.sheet_by_index(0)
tbB = dict()
for r in range(4, wst_b.nrows):
    # 提取有效日期数据
    tdata = [x.strip(";").split(";") for x in wst_b.row_values(r)[11 : wst_b.ncols]]
    tbB[wst_b.row_values(r)[0]] = [[gettime(x[0]), gettime(x[-1])] for x in tdata]

print(len(tbA), len(tbB))
tbC = dict()


def isdt(x):
    return type(x) == datetime.datetime


def sub_seconds(a, b):
    try:
        a1 = (a[0] - b[0]).seconds - 24 * 60  
        print(a1)
    except:
        a1 = 0

    try:
        a2 = (a[1] - b[1]).seconds - 24 * 60  
        print(a2)
    except:
        a2 = 0


for x in tbA.keys():
    for y in range(len(tbB[x])):
        print(tbA[x][y][0], tbB[x][y][0])
        print(tbA[x][y][1], tbB[x][y][1])

        print(sub_seconds(tbA[x][y], tbB[x][y]))
